---
title: "SQL-009: Officer of notice lookup"
summary: Retrieve the current officer-of-notice name for a policy.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-08-21
library: SAIF.Business.View.Policy
reference_type: embedded
---

# SQL-009 Officer of notice lookup

Retrieve the current officer-of-notice name for a policy.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-009 |
| **Operation type** | Read |
| **Execution method** | StringBuilder-assembled |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsPolicyAddress` |
| **Criteria property** | `RetrieveOfficerOfNoticeSql` |
| **Lifecycle method** | `OfficerOfNotice` property |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNum` | Input | Decimal | N/A | Policy number |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [BUSINESS_AFFILIATE](../database-tables.md#business_affiliate) | SELECT / JOIN | `POLICY_NUM`, `OFFICER_OF_NOTICE_FLAG`, `AFFILIATE_CANCEL_DATE`, `AFFILIATE_SSN |
| [AFFILIATE](../database-tables.md#affiliate) | SELECT / JOIN | `AFFILIATE_FIRST_NAME`, `AFFILIATE_INITIAL`, `AFFILIATE_LAST_NAME`, `AFFILIATE_SSN |

## Sample SQL

```sql
SELECT
  TRIM(REPLACE(
    NVL(aff.AFFILIATE_FIRST_NAME, '') || ' ' ||
    NVL(aff.AFFILIATE_INITIAL, '') || ' ' ||
    NVL(aff.AFFILIATE_LAST_NAME, ''), '  ', ' ')) OON
FROM BUSINESS_AFFILIATE ba,
  AFFILIATE aff
WHERE policy_num = :PolicyNum
  AND officer_of_notice_flag = 'Y'
  AND NVL(ba.AFFILIATE_CANCEL_DATE, SYSDATE + 1) > SYSDATE
  AND aff.affiliate_ssn = ba.AFFILIATE_SSN
```


## Operations and processes
- [Retrieve addresses](../operations-processes.md)

## Business rules
- [BR-VP-010: Demo users see masked data](../business-rules.md#br-vp-010-demo-users-see-masked-data)

## Sources
- `SAIF.Business.View.Policy/clsPolicyAddress.vb`